package www.cskaoyan.com.dao;

import www.cskaoyan.com.bean.ClassManager;
import www.cskaoyan.com.bean.Group;
import www.cskaoyan.com.dao.JDBCUtils.ConnectionPool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GroupManagerDao {

    Connection connection;

    public List<Group> getGroupList() throws SQLException {
        connection = ConnectionPool.getConnection();


        String sql = "select g.id, g.groupName,g.classId,  c.className   from `group` g  " +
                "left join `class` c on g.classId = c.id ";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();


        List<Group> list = new ArrayList<>();
        while (resultSet.next()){

            int id = resultSet.getInt(1);
            String groupName  = resultSet.getString(2);
            int classId = resultSet.getInt(3);
            String className  = resultSet.getString(4);

            Group group = new Group();
            group.setId(id);
            group.setGroupName(groupName);
            group.setClassId(classId);
            group.setClassName(className);

            list.add(group);
        }

        return list;
    }

    // 创建分组列表:  根据组名 和 班级 id
    public void createGroup(String groupName, Integer classId) throws SQLException {
        connection = ConnectionPool.getConnection();

        String sql = "insert into `group` set  groupName = ? , classId = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, groupName);
        preparedStatement.setInt(2, classId);
        preparedStatement.executeUpdate();
    }

    // 根据班级id获取分组id
    public List<Group> selectGroupListByClassID(int classId) throws SQLException {

        connection = ConnectionPool.getConnection();


        String sql = "select g.id  from `group`  g" +
                " where  g.classId = ? ";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, classId);
        ResultSet resultSet = preparedStatement.executeQuery();


        List<Group> list = new ArrayList<>();
        while (resultSet.next()){
            int id = resultSet.getInt(1);

            Group group = new Group();
            group.setId(id);
            list.add(group);
        }

        return list;
    }

    // 根据id删除对应分组
    public void deleteGroup(Integer groupId) throws SQLException {
        connection = ConnectionPool.getConnection();

        String sql = "delete from `group` where id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, groupId);
        preparedStatement.executeUpdate();
    }

    // 修改分组信息
    public void changeGroup(Integer groupId, Integer classId, String groupName) throws SQLException {
        connection = ConnectionPool.getConnection();

        String sql = "update `group` set classId=?,groupName=? where id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, classId);
        preparedStatement.setString(2, groupName);
        preparedStatement.setInt(3, groupId);
        preparedStatement.executeUpdate();
    }
}
